"""
excel文件读写操作
Powered By cc
2020/04/04
"""
from shutil import copyfile

from xlutils.copy import copy

import xlrd
import os
import openpyxl
import xlwt


class ExcelReader():
    """
    进行excel的读操作
    """

    def __init__(self):
        #整个excel的缓存
        self.workbook = None
        #当前的sheet页面
        self.sheet = None

        #当前sheet页的最大行数
        self.rows = 0
        self.sheetNum = 0
        #标记读取excel文件的类型,默认是Ture就是使用xlrd打开
        self.excelFlg = True
        #最大的列数
        self.cols = 0



    def open_excel(self,excelPath):
        """
        打开excel文件,并默认使用第一个sheet页
        :param excelPath: excel的路径
        :return: 成功或者失败
        """
        if not os.path.isfile(excelPath):
            return False
        #如果是以.xls的excel文件就使用xlrd打开
        if excelPath.endswith('.xls'):
            self.excelFlg = True
            xlrd.Book.encoding = "utf8"
            self.workbook = xlrd.open_workbook(filename=excelPath)
            #更新该excel的sheet个数
            self.sheetNum = len(self.workbook.sheets())
            #使用第一个sheet页
            self.sheet = self.workbook.sheet_by_index(0)
            #更新当前sheet页的行数
            self.rows = self.sheet.nrows
        else:
            # 如果不是xls结尾的文件就使用openpyxl打开,并将excel类型的标记改为Ture
            self.excelFlg = False
            openpyxl.Workbook.encoding='utf8'
            #默认使用只读的模式打开
            self.workbook = openpyxl.load_workbook(filename=excelPath,read_only=True)
            #指定读取第一个sheet页
            self.sheet = self.workbook[self.workbook.sheetnames[0]]
            #获取sheet个数
            self.sheetNum = len(self.workbook.sheetnames)
            self.rows = self.sheet.max_row
            self.cols = self.sheet.max_column

        return True

    def switchSheetByIndex(self,index=0):
        """
        切换sheet页通过下标
        :param index:
        :return: 成功或者失败
        """
        #下标比总的sheet页个数大，就返回失败
        if index >= self.sheetNum:
            return False
        if self.excelFlg:
            self.sheet = self.workbook.sheet_by_index(index)
            #更新当前页的最大行数
            self.rows  = self.sheet.nrows
        else:
            self.sheet = self.workbook[self.workbook.sheetnames[index]]
            self.rows = self.sheet.max_row
            self.cols = self.sheet.max_column
        return True

    def readLine(self,line):
        """
        读取指定行数的内容，返回是一个列表
        :param line:指定读取的行数
        :return:
        """
        try:
            #进行整数处理
            line = int(line)
        except Exception as e:
            return None
        #如果line比最大行数还要大，返回None
        if line >= self.rows:
            return None
        if self.excelFlg:
            list1 = self.sheet.row_values(line)
            for i in range(len(list1)):
                list1[i] = str(list1[i])
        else:
            list1=[]
            #读取该行的每一列
            for i in range(1,self.cols+1):
                if self.sheet.cell(row=line+1,column=i).value is None:
                    list1.append('')
                else:
                    list1.append(str(self.sheet.cell(row=line+1,column=i).value))

        return list1

    def readLines(self):
        """
        读取该sheet中的所有行
        :return:返回一个二维的列表
        """
        list1 = []
        for i in range(self.rows):
            list1.append(self.readLine(i))
        return list1

class ExcelWriter():
    """
    excel文件的写操作
    Powered By cc
    2020/04/04
    """
    def __init__(self):
        #操作workbook对象
        self.workbook = None
        #当前工作的sheet页
        self.sheet = None
        #存储excel的路径
        self.savePath = None
        #最大的sheet页个数，默认是0,只有复制时才有这个操作
        self.sheetNum = 0
        #excel类型的标记默认是True使用xlwt编写
        self.excelFlg = True



    def   openNewExcel(self,excelPath):
        """
        创建一个崭新的excel进行写操作,通过路径的结尾进行判断使用什么打开
        :param excelPath:
        :return:
        """
        if excelPath.endswith('.xls'):
            self.excelFlg = True
            self.workbook = xlwt.Workbook(encoding='utf-8')
            self.sheet = self.workbook.add_sheet("sheet1")
            self.savePath = excelPath
        else:
            #如果结尾不是xls就是用openpyxl打开进行编写
            self.excelFlg = False
            #设置编码
            openpyxl.Workbook.encoding = 'utf8'

            self.workbook = openpyxl.Workbook()
            self.sheet = self.workbook.create_sheet('sheet1')
            self.savePath = excelPath
            pass

    def openCopyExcel(self,saveExcelPath,targetExcelPath):
        """
        将excel文件复制并打开
        :param saveExcelPath: 存储的excel的路径
        :param targetExcelPath: 复制的execl路径
        :return:
        """
        #复制的目标是否存在，不存在直接return
        if not os.path.isfile(targetExcelPath):
            return False
        if targetExcelPath.endswith('.xls'):
            #将要报存的excel的路径更新
            self.savePath =saveExcelPath[0:saveExcelPath.rfind('.')] + '.xls'
            self.excelFlg = True
            #打开需要复制的excel文件
            readWorkbook = xlrd.open_workbook(filename=targetExcelPath,formatting_info=True)
            #复制
            self.workbook = copy(readWorkbook)
            self.sheet = self.workbook.get_sheet(0)
            #更新最大的sheet个数
            self.sheetNum = len(readWorkbook.sheets())
        else:

            # 将要报存的excel的路径更新
            self.savePath =saveExcelPath[0:saveExcelPath.rfind('.')] + '.xlsx'
            print(self.savePath)
            #标记文件的类型为False
            self.excelFlg = False
            copyfile(targetExcelPath,self.savePath)
            openpyxl.Workbook.encoding = 'utf8'
            #打开复制的文件
            self.workbook = openpyxl.load_workbook(filename=self.savePath)
            #默认使用第一个sheet页
            self.sheet = self.workbook[self.workbook.sheetnames[0]]
            #更新最大sheet个数
            self.sheetNum = len(self.workbook.sheetnames)
        return True

    def addSheet(self,sheetName):
        """
        给excel添加sheet通名称
        :param sheetName:
        :return:
        """
        #对名称进行非空处理
        if sheetName is None or sheetName =='':
            return False
        #如果是True就是用xlrd
        if self.excelFlg:
            # 名称已经存在，也直接return
            if sheetName in self.workbook.sheet_names():
                return False
            self.sheet = self.workbook.add_sheet(sheetName)
        else:
            if sheetName in self.workbook.sheetnames:
                return False
            self.sheet = self.workbook.create_sheet(sheetName)
        return True

    def switchSheetByIndex(self,index):
        """
        通过index切换sheet页
        :param index:
        :return:
        """
        #如果sheetNum是0说明没有通过复制excel文件直接retrun
        if self.sheetNum == 0:
            return False
        if index >= self.sheetNum:
            return False
        if self.excelFlg:
            self.sheet = self.workbook.get_sheet(index)
        else:
            self.sheet = self.workbook[self.workbook.sheetnames[index]]
        return True


    def writeCell(self,row,col,value):
        """
        在第几行和第几列进行写入内容
        :param row: 第几行
        :param col: 第几列
        :param value: 写入的内容
        :return:
        """
        if self.excelFlg:

            self.sheet.write(row,col,value)
        else:
            self.sheet.cell(row=row+1,column=col+1).value = value

    def writeLines(self, valueList, rowNum=0, colNum=0):
        """
        定义写入多少行内容
        :param valuelist: 二维列表
        :param rownum: 从第几行开始写
        :param colnum: 从第几列开始写
        :return:
        """
        rows = len(valueList)  # 获取要写入的行数
        if self.excelFlg:
            for r in range(rows):
                cols = len(valueList[r])  # 获取要写入的列数
                for c in range(cols):
                    self.sheet.write(r + rowNum, c + colNum, str(valueList[r][c]))
        else:
            for r in range(rows):
                cols = len(valueList[r])
                for c in range(cols):
                    self.sheet.cell(row=rowNum+r+1,column=colNum+c+1).value = str(valueList[r][c])


    def saveClose(self):
        """
        保存并关闭excel
        :return:
        """
        self.workbook.save(self.savePath)



if __name__=='__main__':

    targatExcelpath = '../lib/testcases/HTTPLogin.xlsx'
    targatExcelpath1 = '../lib/testcases/HTTPLogin.xls'
    saveExcelPath = '../lib/result/resultHTTPLogin.xls'
    writePath = '../lib/result/test.xls'

    wr = ExcelReader()
    wr.open_excel(targatExcelpath)
    #wr.open_excel(targatExcelpath1)
    for j in range(wr.sheetNum):
        wr.switchSheetByIndex(j)
        for i in range(wr.rows):
            print(wr.readLine(i))
    print(wr.readLines())

    print('***********************************************************')




